package jmind.core.poi;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.Collections;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import jmind.core.support.BeanProperty;
import jmind.core.support.MethodSupport;
import jmind.base.util.DataUtil;
import jmind.base.util.DateUtil;
import jmind.base.util.MethodUtil;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

import com.google.common.collect.Lists;

/**
 * 老板xls  excel
 * @author xieweibo
 * @date 2015年7月24日
 */
public class XlsExcel extends Excel {

    final static XlsExcel INSTANCE = new XlsExcel();

    /**
     * 创建Excel当前sheet页的头信息
     * 
     * @param sheet
     *            : Excel工作簿的一个sheet
     *            : sheet头信息列表(sheet第一行各列值)
     */
    private void createTitle(HSSFSheet sheet, String[] title) {
        HSSFRow row = sheet.createRow(0); // 创建该页的一行
        HSSFCell cell = null;
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i); // 创建该行的一列
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(title[i]);
        }

    }

    @Override
    public Workbook exportExcel(String sheetName, List<?> datas) {
        // 创建工作簿（Excel文件）
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 新增的四句话，设置CELL格式为文本格式
        HSSFCellStyle textCellStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        textCellStyle.setDataFormat(format.getFormat("@"));
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建Sheet页的文件体
        for (int rownum = 0; rownum < datas.size(); rownum++) {
            HSSFRow row = sheet.createRow(rownum);
            HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
            cell.setCellStyle(textCellStyle);
            cell.setCellValue(datas.get(rownum).toString());
        }
        return workbook;
    }

    @Override
    public <T> Workbook exportExcel(String sheetName, List<T> dataset, String[] headers,String dateFormart) {
        // 创建工作簿（Excel文件）
        HSSFWorkbook workbook = new HSSFWorkbook();
        if (DataUtil.isEmpty(dataset))
            return workbook;
        // 新增的四句话，设置CELL格式为文本格式
        HSSFCellStyle textCellStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        textCellStyle.setDataFormat(format.getFormat("@"));
        HSSFSheet sheet = workbook.createSheet(sheetName);

        Field[] fields = dataset.get(0).getClass().getDeclaredFields();
        if (headers == null) {
            headers = new String[fields.length];
            for (int i = 0; i < fields.length; i++) {
                headers[i] = fields[i].getName();
            }
        }
        // 创建Sheet页的文件头（第一行）
        createTitle(sheet, headers);
        // 创建Sheet页的文件体（后续行）
        for (int rownum = 0; rownum < dataset.size(); rownum++) {
            Object obj = dataset.get(rownum);
            HSSFRow row = sheet.createRow(rownum + 1);
            for (int columnIndex = 0; columnIndex < fields.length; columnIndex++) {
                try {
                    HSSFCell cell = row.createCell(columnIndex, HSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle(textCellStyle);
                    //BeanUtilsBean.getInstance().getPropertyUtils().getProperty(obj,  fields[columnIndex].getName());
                    Object value = MethodSupport.getPropertyValue(obj, fields[columnIndex].getName());
                    if (value == null) {
                        continue;
                    }
                    if (value instanceof Date) {
                        cell.setCellValue(DateUtil.format((Date) value, dateFormart));
                    } else {
                        cell.setCellValue(value.toString());
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }
        return workbook;
    }

    @Override
    public <T> Workbook exportExcel(String sheetName, List<T> dataset, String[] headers, String[] methods,String dateFormart) {
        // 创建工作簿（Excel文件）
        HSSFWorkbook workbook = new HSSFWorkbook();
        if (DataUtil.isEmpty(dataset))
            return workbook;
        // 新增的四句话，设置CELL格式为文本格式
        HSSFCellStyle textCellStyle = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        textCellStyle.setDataFormat(format.getFormat("@"));
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建Sheet页的文件头（第一行）
        createTitle(sheet, headers);
        // 创建Sheet页的文件体（后续行）
        for (int rownum = 0; rownum < dataset.size(); rownum++) {
            Object obj = dataset.get(rownum);
            HSSFRow row = sheet.createRow(rownum + 1);
            for (int columnIndex = 0; columnIndex < methods.length; columnIndex++) {
                try {
                    HSSFCell cell = row.createCell(columnIndex, HSSFCell.CELL_TYPE_STRING);
                    cell.setCellStyle(textCellStyle);
                    Object value = MethodUtil.invokeMethod(obj, methods[columnIndex]);
                    if (value == null) {
                        continue;
                    }
                    if (value instanceof Date) {
                        cell.setCellValue(DateUtil.format((Date) value, dateFormart));
                    } else {
                        cell.setCellValue(value.toString());
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        }

        return workbook;
    }

    @Override
    public Workbook exportMapExcel(String sheetName, List<Map<String, String>> objList) {
        if (DataUtil.isEmpty(objList))
            return null;
        // 创建工作簿（Excel文件）
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建Excel工作簿的第一个Sheet页
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 创建Sheet页的文件体
        for (int i = 0; i < objList.size(); i++) {
            Map<String, String> map = objList.get(i);
            HSSFRow row = sheet.createRow(i);
            int j = 0;
            for (String k : map.values()) {
                HSSFCell cell = row.createCell(j++);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(k);
            }
        }
        return workbook;
    }

    @Override
    public List<String> excel2StrList(InputStream in) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(in);
            HSSFSheet sheet = workbook.getSheetAt(0);
            List<String> list = Lists.newArrayListWithExpectedSize(sheet.getPhysicalNumberOfRows());
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                HSSFRow row = sheet.getRow(i);
                try {
                    HSSFCell cell = row.getCell(0);
                    // 全部当中String 处理， getStringCellValue 时才不报错
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    String value = cell.getStringCellValue();
                    if (value != null) {
                        list.add(value);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return Collections.emptyList();
        }
    }

    @Override
    public <T> List<T> excel2List(InputStream in, Class<T> claz, String[] fields, int start) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(in);
            HSSFSheet sheet = workbook.getSheetAt(0);
            List<T> list = Lists.newArrayListWithExpectedSize(sheet.getPhysicalNumberOfRows());
            if (fields == null) { // 没定义 字段，从excel读出
                HSSFRow row = sheet.getRow(0);
                fields = new String[row.getPhysicalNumberOfCells()];
                for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                    fields[i] = row.getCell(i).getStringCellValue();
                }
            }
            for (int i = start; i < sheet.getPhysicalNumberOfRows(); i++) {
                BeanProperty<T> bean = new BeanProperty<T>(claz);
                HSSFRow row = sheet.getRow(i);
                for (int j = 0; j < fields.length; j++) {
                    if (DataUtil.isEmpty(fields[j]))
                        continue;
                    try {
                        HSSFCell cell = row.getCell(j);
                        // 全部当中String 处理， getStringCellValue 时才不报错
                        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                        //  BeanUtils.setProperty(obj, fields[j], cell.getStringCellValue());
                        //     ConvertUtils.convert(value, clazz)
                        String value = cell.getStringCellValue();
                        if (value != null) {
                            bean.setProperty(fields[j], value.trim());
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                list.add(bean.get());
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return Collections.emptyList();
        }

    }

    @Override
    public List<Map<String, String>> excel2Map(InputStream in, String[] strKey) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(in);
            // 读取第一章表格内容
            HSSFSheet sheet = workbook.getSheetAt(0);
            List<Map<String, String>> listMap = Lists.newArrayListWithExpectedSize(sheet.getPhysicalNumberOfRows());
            // 定义 row、cell
            HSSFRow row;
            HSSFCell cell;
            // 循环输出表格中的内容
            for (int rowIndex = sheet.getFirstRowNum() + 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
                row = sheet.getRow(rowIndex);
                if (row == null || row.getFirstCellNum() < 0)
                    break;
                Map<String, String> map = new LinkedHashMap<String, String>();
                for (int cellIndex = row.getFirstCellNum(); cellIndex < strKey.length; cellIndex++) {
                    // 通过 row.getCell(j).toString() 获取单元格内容，
                    String key = strKey[cellIndex];
                    cell = row.getCell(cellIndex);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String value = cell.getStringCellValue();
                    map.put(key, value);
                }
                listMap.add(map);
            }
            return listMap;

        } catch (Exception e) {
            e.printStackTrace();
            return Collections.emptyList();
        }

    }

}
